﻿/*******************************************************************************
 * Copyright @ 2017 YunSpace.Framework 版权所有
 * Author: GaoTang
 * Description: YunSpace 快速开发平台
 * Website：http://www.yunspace.com.cn
*********************************************************************************/
#region USING SYSTEM
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using YunSpace.Data;
using YunSpace.Data.Extensions;
using YunSpace.Domain.Entity.WorkFlow;
using YunSpace.Domain.IRepository.WorkFlow;
#endregion

namespace YunSpace.Repository.WorkFlow
{
    public class DictionaryRepository : IDictionaryRepository
    {
        private MySqlDbHelper dbHelper = new MySqlDbHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public DictionaryRepository()
        {
        }

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">DictionaryEntity实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add(DictionaryEntity model)
        {
            //string sql = @"INSERT INTO WorkFlow_Dictionary
            //(ID,ParentID,Title,Code,Value,Note,Other,Sort) 
            //VALUES(@ID,@ParentID,@Title,@Code,@Value,@Note,@Other,@Sort)";
            //        MySqlParameter[] parameters = new MySqlParameter[]{
            //            new MySqlParameter("@ID", MySqlDbType.VarChar, 50, -1){ Value = model.ID },
            //            new MySqlParameter("@ParentID", MySqlDbType.VarChar, 50, -1){ Value = model.ParentID },
            //            new MySqlParameter("@Title", MySqlDbType.NVarChar, -1){ Value = model.Title },
            //            model.Code == null ? new MySqlParameter("@Code", MySqlDbType.VarChar, 500) { Value = DBNull.Value } : new MySqlParameter("@Code", MySqlDbType.VarChar, 500) { Value = model.Code },
            //            model.Value == null ? new MySqlParameter("@Value", MySqlDbType.VarChar, -1) { Value = DBNull.Value } : new MySqlParameter("@Value", MySqlDbType.VarChar, -1) { Value = model.Value },
            //            model.Note == null ? new MySqlParameter("@Note", MySqlDbType.VarChar, -1) { Value = DBNull.Value } : new MySqlParameter("@Note", MySqlDbType.VarChar, -1) { Value = model.Note },
            //            model.Other == null ? new MySqlParameter("@Other", MySqlDbType.VarChar, -1) { Value = DBNull.Value } : new MySqlParameter("@Other", MySqlDbType.VarChar, -1) { Value = model.Other },
            //            new MySqlParameter("@Sort", MySqlDbType.Int, -1){ Value = model.Sort }
            //        };
            //        return dbHelper.Execute(sql, parameters);
            return -1;
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">DictionaryEntity实体类</param>
        public int Update(DictionaryEntity model)
        {
            //string sql = @"UPDATE WorkFlow_Dictionary SET 
            //ParentID=@ParentID,Title=@Title,Code=@Code,Value=@Value,Note=@Note,Other=@Other,Sort=@Sort
            //WHERE ID=@ID";
            //        MySqlParameter[] parameters = new MySqlParameter[]{
            //            new MySqlParameter("@ParentID", MySqlDbType.VarChar, 50, -1){ Value = model.ParentID },
            //            new MySqlParameter("@Title", MySqlDbType.NVarChar, -1){ Value = model.Title },
            //            model.Code == null ? new MySqlParameter("@Code", MySqlDbType.VarChar, 500) { Value = DBNull.Value } : new MySqlParameter("@Code", MySqlDbType.VarChar, 500) { Value = model.Code },
            //            model.Value == null ? new MySqlParameter("@Value", MySqlDbType.VarChar, -1) { Value = DBNull.Value } : new MySqlParameter("@Value", MySqlDbType.VarChar, -1) { Value = model.Value },
            //            model.Note == null ? new MySqlParameter("@Note", MySqlDbType.VarChar, -1) { Value = DBNull.Value } : new MySqlParameter("@Note", MySqlDbType.VarChar, -1) { Value = model.Note },
            //            model.Other == null ? new MySqlParameter("@Other", MySqlDbType.VarChar, -1) { Value = DBNull.Value } : new MySqlParameter("@Other", MySqlDbType.VarChar, -1) { Value = model.Other },
            //            new MySqlParameter("@Sort", MySqlDbType.Int, -1){ Value = model.Sort },
            //            new MySqlParameter("@ID", MySqlDbType.VarChar, 50, -1){ Value = model.ID }
            //        };
            //        return dbHelper.Execute(sql, parameters);
            return -1;
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(string id)
        {
            string sql = "DELETE FROM WorkFlow_Dictionary WHERE F_Id=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = id }
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<DictionaryEntity> DataReaderToList(MySqlDataReader dataReader)
        {
            List<DictionaryEntity> List = new List<DictionaryEntity>();
            DictionaryEntity model = null;
            while (dataReader.Read())
            {
                model = new DictionaryEntity();
                model.ID = dataReader.GetString(0);
                model.ParentID = dataReader.GetString(1);
                model.Title = dataReader.GetString(2);
                if (!dataReader.IsDBNull(3))
                    model.Code = dataReader.GetString(3);
                if (!dataReader.IsDBNull(4))
                    model.Value = dataReader.GetString(4);
                if (!dataReader.IsDBNull(5))
                    model.Note = dataReader.GetString(5);
                if (!dataReader.IsDBNull(6))
                    model.Other = dataReader.GetString(6);
                model.Sort = dataReader.GetInt32(7);
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<DictionaryEntity> GetAll()
        {
            string sql = "SELECT * FROM WorkFlow_Dictionary";
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<DictionaryEntity> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM WorkFlow_Dictionary";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public DictionaryEntity Get(string id)
        {
            string sql = "SELECT * FROM WorkFlow_Dictionary WHERE F_Id=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = id }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<DictionaryEntity> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }


        /// <summary>
        /// 查询根记录
        /// </summary>
        public DictionaryEntity GetRoot()
        {
            string sql = "SELECT * FROM WorkFlow_Dictionary WHERE F_ParentId=@ParentID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ParentID", MySqlDbType.VarChar, 50){ Value = string.Empty }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<DictionaryEntity> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 查询下级记录
        /// </summary>
        public List<DictionaryEntity> GetChilds(string id)
        {
            string sql = "SELECT * FROM WorkFlow_Dictionary WHERE F_ParentId=@ParentID ORDER BY Sort";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ParentID", MySqlDbType.VarChar, 50){ Value = id }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<DictionaryEntity> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询下级记录
        /// </summary>
        public List<DictionaryEntity> GetChildsByCode(string code)
        {
            string sql = "SELECT * FROM WorkFlow_Dictionary WHERE F_ParentId=(SELECT F_Id FROM WorkFlow_Dictionary WHERE F_Code=@Code) ORDER BY F_Sort";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@Code", MySqlDbType.VarChar){ Value = code }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<DictionaryEntity> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }

        /// <summary>
        /// 查询上级记录
        /// </summary>
        public DictionaryEntity GetParent(string id)
        {
            string sql = "SELECT * FROM WorkFlow_Dictionary WHERE F_Id=(SELECT F_ParentId FROM WorkFlow_Dictionary WHERE F_Id=@ID)";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = id }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<DictionaryEntity> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 是否包含下级记录
        /// </summary>
        public bool HasChilds(string id)
        {
            string sql = "SELECT ID FROM WorkFlow_Dictionary WHERE F_ParentId=@ParentID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ParentID", MySqlDbType.VarChar, 50){ Value = id }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            bool has = dataReader.HasRows;
            dataReader.Close();
            return has;
        }

        /// <summary>
        /// 得到最大排序
        /// </summary>
        public int GetMaxSort(string id)
        {
            string sql = "SELECT MAX(F_Sort)+1 FROM WorkFlow_Dictionary WHERE F_ParentId=@ParentID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ParentID", MySqlDbType.VarChar, 50){ Value = id }
            };
            string max = dbHelper.GetFieldValue(sql, parameters);
            int max1;
            return max.IsInt(out max1) ? max1 : 1;
        }

        /// <summary>
        /// 更新排序
        /// </summary>
        public int UpdateSort(string id, int sort)
        {
            string sql = "UPDATE WorkFlow_Dictionary SET F_Sort=@Sort WHERE F_Id=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@Sort", MySqlDbType.Int32){ Value = sort },
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = id }
            };
            return dbHelper.Execute(sql, parameters);
        }

        /// <summary>
        /// 根据代码查询一条记录
        /// </summary>
        public DictionaryEntity GetByCode(string code)
        {
            string sql = "SELECT * FROM WorkFlow_Dictionary WHERE F_Code=@Code";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@Code", MySqlDbType.VarChar){ Value = code }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<DictionaryEntity> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }
    }
}

